const mysql = require('mysql');
const fs = require('fs');
const sourceDbConf = {
    host: 'localhost',
    port: 8859,
    user: 'root',
    password: 'yh85yzaKZbe42jDL5Vds4cNxemy5FWRj',
    database: 'stage_test'
};
const targetDbConf = {
    host: 'localhost',
    port: 8859,
    user: 'root',
    password: 'yh85yzaKZbe42jDL5Vds4cNxemy5FWRj',
    database: 'stage_test'
};

class SqlFile {
    constructor(path){
        this.path = path;
        this.sqlBuff = '';
    }
    log(string) {
        this.sqlBuff += string.replace(/\r\n/g, ' ')  + '\n';
    }
    startAlterTable(tableName) {
        if (this.sqlBuff[this.sqlBuff.length - 1] !== ';') {
            this.sqlBuff += ';';
        }
        this.sqlBuff += `ALTER TABLE ${tableName} `;
    }
    end() {
        // console.log(this.sqlBuff);
        // let now = new Date();
        // fs.open(`test2.${now.getFullYear()}${now.getMonth()}${now.getDate()}.patch.sql`,(e,fd) => {
        //     if(e) throw e;
        //     fs.write(fd, this.sqlBuff, (e) => {
        //         if(e) throw e;
        //         fs.closeSync(fd);
        //     })
        // });

    }
}
class Database {
    constructor(host, port, user, password, database) {
        this.database = database;
        this.connection = mysql.createConnection({
            host: host,
            port: port,
            user: user,
            password: password,
            database: database
        });
        this.connection.connect();
    }
    getTables() {
        return new Promise((resolve, reject) => {
            this.connection.query('show tables', (err, rows) => {
                if (err) {
                    reject(err);
                }
                let tables = [];
                for (let row of rows) {
                    tables.push(row[`Tables_in_${this.database}`]);
                }
                resolve(tables);
            });
        });
    }
    getTablePrimaryKeys(tableName) {
        return new Promise((resolve, reject) => {
            this.connection.query(`SELECT k.column_name
                FROM information_schema.table_constraints t
                JOIN information_schema.key_column_usage k
                USING (constraint_name,table_schema,table_name)
                WHERE t.constraint_type='PRIMARY KEY'
                AND t.table_schema='${this.database}'
                AND t.table_name='${tableName}'`, (err, rows) => {
                    if (err) {
                        reject(err);
                    }
                    resolve(rows);
                });
        });

    }
    getTableUniqueKeys(tableName) {
        return new Promise((resolve, reject) => {
            this.connection.query(`SELECT k.column_name
                FROM information_schema.table_constraints t
                JOIN information_schema.key_column_usage k
                USING (constraint_name,table_schema,table_name)
                WHERE t.constraint_type='UNIQUE' 
                AND t.table_schema='${this.database}'
                AND t.table_name='${tableName}'`, (err, rows) => {
                    if (err) {
                        reject(err);
                    }
                    resolve(rows);
                });
        });

    }
    getTableForeignKeys(tableName) {
        return new Promise((resolve, reject) => {
            this.connection.query(`SELECT
                CONSTRAINT_NAME,
                column_name AS 'FOREIGN',  
                concat(referenced_table_name, '(', referenced_column_name, ')') AS 'REFERENCES'
                FROM
                information_schema.key_column_usage
                WHERE
                referenced_table_name IS NOT NULL
                AND table_schema = '${this.database}'
                AND table_name = '${tableName}'`, (err, rows) => {
                    if (err) {
                        reject(err);
                    }
                    resolve(rows);
                });
        });
    }
    getTableFields(tableName) {
        return new Promise((resolve, reject) => {
            this.connection.query(`desc ${tableName}`, (err, rows) => {
                if (err) {
                    reject(err);
                }
                resolve(rows);
            });
        });
    }
    getTableCreateSql(tableName) {
        return new Promise((resolve, reject) => {
            this.connection.query(`show create table ${tableName}`, (err, rows) => {
                if (err) {
                    reject(err);
                }
                resolve(rows[0]['Create Table']);
            });
        });
    }
    transferRowsToId(rows) {
        let id = '';
        for (let row of rows) {
            row.compareTime = 0;
            id += `Field:${row.Field}Type:${row.Type}Null:${row.Null}Key:${row.Key}Default:${row.Default}Extra:${row.Extra}`;
        }
        return id;
    }
    transferKeysToId(keys) {
        let id = '';
        for (let key of keys) {
            id += `name:${key.column_name}`;
        }
        return id;
    }
    transferForeignKeysToId(keys) {
        let id = '';
        for (let key of keys) {
            id += `CONSTRAINT_NAME:${key.CONSTRAINT_NAME}FOREIGN:${key.FOREIGN}REFERENCES:${key.REFERENCES}`;
        }
        return id;
    }
    transferArrayToMap(arr, key) {
        let arrMap = new Map();
        arr.forEach(item => {
            arrMap[item[key]] = item;
        });
        return arrMap;
    }
    async getTableMap() {
        let tables = await this.getTables();
        let tableMap = {};
        for (let tableName of tables) {
            tableMap[tableName] = {};
            let fields = await this.getTableFields(tableName);
            let primaryKeys = await this.getTablePrimaryKeys(tableName);
            let foreignKeys = await this.getTableForeignKeys(tableName);
            let uniqueKeys = await this.getTableUniqueKeys(tableName);
            tableMap[tableName].fields = this.transferArrayToMap(fields, 'Field');
            tableMap[tableName].id = this.transferRowsToId(fields);
            tableMap[tableName].primaryKeys = this.transferArrayToMap(primaryKeys, 'column_name');
            tableMap[tableName].primaryKeysId = this.transferKeysToId(primaryKeys);
            tableMap[tableName].foreignKeys = this.transferArrayToMap(foreignKeys, 'CONSTRAINT_NAME');
            tableMap[tableName].foreignKeysId = this.transferForeignKeysToId(foreignKeys);
            tableMap[tableName].uniqueKeys = this.transferArrayToMap(uniqueKeys, 'column_name');
            tableMap[tableName].uniqueKeysId = this.transferKeysToId(uniqueKeys);
            tableMap[tableName].compareTime = 0;
        }
        return tableMap;

    }
    end() {
        this.connection.end();
    }
}
let compareFields = (sqlFile, tableName, sourceFields, targetFields) => {
    let lastField = 'FIRST';
    for (let sourceField in sourceFields) {
        if (!targetFields[sourceField]) {
            // 可能无表，可能重命名
            sourceFields[sourceField].compareTime = 0;
        } else if (targetFields[sourceField].Type !== sourceFields[sourceField].Type
            || targetFields[sourceField].Null !== sourceFields[sourceField].Null
            || targetFields[sourceField].Key !== sourceFields[sourceField].Key
            || targetFields[sourceField].Default !== sourceFields[sourceField].Default
            || targetFields[sourceField].Extra !== sourceFields[sourceField].Extra) {
            // 列属性不同
            sourceFields[sourceField].compareTime = 1;
            targetFields[sourceField].compareTime = 1;
            sqlFile.log(`ALTER TABLE ${tableName} MODIFY COLUMN ${sourceField} ${sourceFields[sourceField].Type}
             ${sourceFields[sourceField].Null === 'NO' ? 'NOT NULL' : ''} ${sourceFields[sourceField].Extra} ${lastField}
             ${sourceFields[sourceField].Key === 'PRI' ? `, ADD PRIMARY KEY (${sourceField}) USING BTREE;` : ';'}`);
            console.log(`ALTER TABLE ${tableName} MODIFY COLUMN ${sourceField} ${sourceFields[sourceField].Type}
             ${sourceFields[sourceField].Null === 'NO' ? 'NOT NULL' : ''} ${sourceFields[sourceField].Extra} ${lastField}
             ${sourceFields[sourceField].Key === 'PRI' ? `, ADD PRIMARY KEY (${sourceField}) USING BTREE;` : ';'}`);
        }
        else {
            sourceFields[sourceField].compareTime = 1;
            targetFields[sourceField].compareTime = 1;
        }
        lastField = ` AFTER ${sourceField}`;
    }
    let comparedSourceFieldsMap = {};
    let comparedTargetFieldsMap = {};
    for (let fieldName in sourceFields) {
        if (sourceFields[fieldName].compareTime === 0) {
            comparedSourceFieldsMap[fieldName] = sourceFields[fieldName];
        }
    }
    for (let fieldName in targetFields) {
        if (targetFields[fieldName].compareTime === 0) {
            comparedTargetFieldsMap[fieldName] = targetFields[fieldName];
        }
    }
    for (let sourceFieldName in comparedSourceFieldsMap) {
        for (let targetFieldName in comparedTargetFieldsMap) {
            if (comparedSourceFieldsMap[sourceFieldName].compareTime === 0
                && comparedTargetFieldsMap[targetFieldName].compareTime === 0
                && comparedSourceFieldsMap[sourceFieldName].Type === comparedTargetFieldsMap[targetFieldName].Type
                && comparedSourceFieldsMap[sourceFieldName].Null === comparedTargetFieldsMap[targetFieldName].Null
                && comparedSourceFieldsMap[sourceFieldName].Key === comparedTargetFieldsMap[targetFieldName].Key
                && comparedSourceFieldsMap[sourceFieldName].Default === comparedTargetFieldsMap[targetFieldName].Default
                && comparedSourceFieldsMap[sourceFieldName].Extra === comparedTargetFieldsMap[targetFieldName].Extra) {
                comparedSourceFieldsMap[sourceFieldName].compareTime = 1;
                comparedTargetFieldsMap[targetFieldName].compareTime = 1;
                // 重命名列
                console.log(`ALTER TABLE ${tableName} CHANGE COLUMN ${targetFieldName} ${sourceFieldName}
                    ${comparedSourceFieldsMap[sourceFieldName].Type} ${comparedSourceFieldsMap[sourceFieldName].Null === 'NO' ? 'NOT NULL' : ''}
                    ${comparedSourceFieldsMap[sourceFieldName].Extra}
                    ${comparedSourceFieldsMap[sourceFieldName].Key === 'PRI' ? `, ADD PRIMARY KEY (${sourceFieldName}) USING BTREE;` : ';'}
                    `);
            }
        }
    }
    lastField = 'FIRST';
    for (let fieldName in sourceFields) {
        if (!sourceFields[fieldName].compareTime) {
            // 加列
            console.log(`
                ALTER TABLE ${tableName} ADD COLUMN ${fieldName} ${sourceFields[fieldName].Type}
                ${sourceFields[fieldName].Null === 'NO' ? 'NOT NULL' : ''} ${sourceFields[fieldName].Extra} ${lastField}
                ${sourceFields[fieldName].Key === 'PRI' ? `, ADD PRIMARY KEY (${fieldName}) USING BTREE;` : ';'}
            `);
        }
        lastField = ` AFTER ${fieldName}`;
    }
    for (let fieldName in comparedTargetFieldsMap) {
        if (!comparedTargetFieldsMap[fieldName].compareTime) {
            // 减列
            console.log(`ALTER TABLE ${tableName} DROP COLUMN ${fieldName};`);
        }
    }
};

let main = async function () {

    let sourceDb = new Database(sourceDbConf);
    let targetDb = new Database(targetDbConf);
    let sourceTablesMap = await sourceDb.getTableMap();
    let targetTablesMap = await targetDb.getTableMap();
    let sqlFile = new SqlFile();
    for (let tableName in sourceTablesMap) {
        if (targetTablesMap[tableName] && targetTablesMap[tableName].id === sourceTablesMap[tableName].id) {
            // 两张表相等
            targetTablesMap[tableName].compareTime = 1;
            sourceTablesMap[tableName].compareTime = 1;
        } else if (!targetTablesMap[tableName]) {
            // 缺表 或 重命名
            sourceTablesMap[tableName].compareTime = 0;
        } else {
            // 表结构不一样
            targetTablesMap[tableName].compareTime = 1;
            sourceTablesMap[tableName].compareTime = 1;
            compareFields(sqlFile, tableName, sourceTablesMap[tableName].fields, targetTablesMap[tableName].fields);
        }
        if (targetTablesMap[tableName] && targetTablesMap[tableName].foreignKeysId !== sourceTablesMap[tableName].foreignKeysId) {
            // 比较外键
            let targetKeys = [...targetTablesMap[tableName].foreignKeys.keys()];
            let sourceKeys = [...sourceTablesMap[tableName].foreignKeys.keys()];
            let union = new Set([...targetKeys, ...sourceKeys]);
            console.log("foreignKeysId");
            console.log(union);
        }
        if (targetTablesMap[tableName] && targetTablesMap[tableName].uniqueKeysId !== sourceTablesMap[tableName].uniqueKeysId) {
            // 比较unique key
            let targetKeys = [...targetTablesMap[tableName].uniqueKeys.keys()];
            let sourceKeys = [...sourceTablesMap[tableName].uniqueKeys.keys()];
            let union = new Set([...targetKeys, ...sourceKeys]);
            console.log("uniqueKeysId");
            console.log(union);
        }
        if (targetTablesMap[tableName] && targetTablesMap[tableName].primaryKeysId !== sourceTablesMap[tableName].primaryKeysId) {
            // 比较primary key
            let targetKeys = [...targetTablesMap[tableName].primaryKey.keys()];
            let sourceKeys = [...sourceTablesMap[tableName].primaryKey.keys()];
            let union = new Set([...targetKeys, ...sourceKeys]);
            console.log(union);
        }
    }
    let comparedSourceTablesMap = {};
    let comparedTargetTablesMap = {};
    for(let tableName in sourceTablesMap) {
        if (sourceTablesMap[tableName].compareTime === 0) {
            comparedSourceTablesMap[tableName] = sourceTablesMap[tableName];
        }
    };
    for (let tableName in targetTablesMap) {
        if (targetTablesMap[tableName].compareTime === 0) {
            comparedTargetTablesMap[tableName] = targetTablesMap[tableName];
        }
    };
    for (let sourceTableName in comparedSourceTablesMap) {
        for (let targetTableName in comparedTargetTablesMap) {
            if (comparedSourceTablesMap[sourceTableName].compareTime === 0
                && comparedTargetTablesMap[targetTableName].compareTime === 0
                && comparedSourceTablesMap[sourceTableName].id === comparedTargetTablesMap[targetTableName].id
                && comparedSourceTablesMap[sourceTableName].foreignKeysId === comparedTargetTablesMap[targetTableName].foreignKeysId
                && comparedSourceTablesMap[sourceTableName].uniqueKeysId === comparedTargetTablesMap[targetTableName].uniqueKeysId
                && comparedSourceTablesMap[sourceTableName].primaryKeysId === comparedTargetTablesMap[targetTableName].primaryKeysId) {
                comparedSourceTablesMap[sourceTableName].compareTime = 1;
                comparedTargetTablesMap[targetTableName].compareTime = 1;
                // 重命名表
                sqlFile.log(`rename table ${targetTableName} to ${sourceTableName};`);
                console.log('rename table ', targetTableName, 'to', sourceTableName);
            }
        }
    }
    
    for (let tableName in comparedSourceTablesMap) {
        if (!comparedSourceTablesMap[tableName].compareTime){
            // 增加表
            let tableDes  = await sourceDb.getTableCreateSql(tableName);
            sqlFile.log(tableDes);
            console.log(tableDes);
        }
    }

    for (let tableName in comparedTargetTablesMap) {
        if (!comparedTargetTablesMap[tableName].compareTime) {
            // 删除表
            sqlFile.log(`drop table ${tableName};`);
            console.log('drop table ', tableName);
        }
    }
    sourceDb.end();
    targetDb.end();
    sqlFile.end();
};
main();
